/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.icee.myth.gm.db;

import com.google.protobuf.CodedInputStream;
import com.icee.myth.gm.admin.JsonUserData;
import com.icee.myth.protobuf.InternalCommonProtocol.PlayerDetailProto;
import com.icee.myth.gm.admin.OperateLog;
import com.icee.myth.gm.admin.OperateLogs;
import com.icee.myth.gm.admin.UserData;
import com.icee.myth.gm.config.ServerConfig;
import com.icee.myth.gm.player.CharDetailInfo;
import com.icee.myth.gm.player.PlayerInfo;
import com.icee.myth.utils.Consts;
import com.icee.myth.utils.LogConsts;
import com.icee.myth.utils.MLogger;
import com.icee.myth.utils.StackTraceUtil;
import java.io.IOException;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author yangyi
 */
public class DbHandler {

    private String dbHost;  // 数据库地址
    private String dbName;  // 数据库名
    public Connection connection;  // 数据库连接

    public DbHandler(String dbHost, String dbName) {
        this.dbHost = dbHost;
        this.dbName = dbName;
    }

    public boolean connectToDB() {
        try {
            if (connection == null || connection.isClosed()) {
                Class.forName("com.mysql.jdbc.Driver");
                System.err.println("Connect to database " + dbHost + "/" + dbName);
                connection = DriverManager.getConnection("jdbc:mysql://" + dbHost + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8", "mythtest", "jkY5qmGKVcRs4nST");
            }
            return true;
        } catch (SQLException ex) {
            System.err.println("Connect to database error : " + StackTraceUtil.getStackTrace(ex));
        } catch (ClassNotFoundException ex) {
            System.err.println("Connect to database error : " + StackTraceUtil.getStackTrace(ex));
        }

        return false;
    }

    public void close() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException ex) {
            System.err.println("close database error : " + StackTraceUtil.getStackTrace(ex));
        }
    }

    public UserData getUserDataFromDB(String username) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call get_user(?)}");
                stmt.setString(1, username);
                rs = stmt.executeQuery();

                if (rs.next()) {
                    return new UserData(username, rs.getString(1), rs.getInt(2));
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

    public boolean updateForbinTalkTime(String passport, int time) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "update thirdpartyuser set forbiddentalkendtime = from_unixtime(" + time + ") where thirdpartyUserId ='" + passport + "';";
                stmt = connection.createStatement();

                if (stmt.executeUpdate(sql) == 1) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

    public boolean updateForbinLoginTime(String passport, int endTime) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "update thirdpartyuser set forbiddenloginendtime = from_unixtime(" + endTime + ") where thirdpartyUserId ='" + passport + "';";
                stmt = connection.createStatement();

                if (stmt.executeUpdate(sql) == 1) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

    public CharDetailInfo getUserDetailInfo(int playerId) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "select * from charactor where mid =" + playerId + ";";
                stmt = connection.createStatement();
                rs = stmt.executeQuery(sql);
                CharDetailInfo charInfo = new CharDetailInfo();
                if (rs.next()) {
                    charInfo.mid = rs.getInt("mid");
                    charInfo.name = rs.getString("name");
                    charInfo.gold1 = rs.getInt("gold1");
                    charInfo.gold2 = rs.getInt("gold2");
                    charInfo.silver = rs.getLong("silver");
                    charInfo.energy = rs.getInt("energy");
                    charInfo.level = rs.getInt("level");
                    charInfo.experience = rs.getInt("experience");
                    charInfo.vipLevel = rs.getInt("vipLevel");
                    charInfo.vipExperience = rs.getInt("vipExperience");

                    charInfo.totalOnlineTime = rs.getLong("totalOnlineTime");

                    Timestamp createTimestamp = rs.getTimestamp("createtime");
                    charInfo.createTime = (createTimestamp != null) ? createTimestamp.getTime() : 0;

                    Timestamp leaveTimestamp = rs.getTimestamp("leavetime");
                    charInfo.leaveTime = (leaveTimestamp != null) ? leaveTimestamp.getTime() : 0;

                    Blob blob = rs.getBlob("detail");
                    if (blob != null) {
                        charInfo.detail = PlayerDetailProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(blob.getBinaryStream())).build();
                    }
                    
                    return charInfo;
                } else {
                    MLogger.getlogger().debuglog(LogConsts.LOGLEVEL_ERROR, "Player[" + playerId + "] not found in database.");
                }
            }
        } catch (SQLException ex) {
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } catch (IOException ex) {
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

//    public StatInfo getStatData(int serverId, String date) {
//        Statement stmt = null;
//        ResultSet rs = null;
//        try {
//            if (connectToDB()) {
//                String sql = "select * from stat.daily where date ='" + date + "';";
//                stmt = dbConnection.createStatement();
//                rs = stmt.executeQuery(sql);
//                StatInfo info = new StatInfo();
//                return info;
//            }
//        } catch (SQLException ex) {
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        }  finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return null;
//    }
    public PlayerInfo getPlayerInfoFromDBByCid(int cid) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call get_playerinfobycid(?)}");
                stmt.setInt(1, cid);
                rs = stmt.executeQuery();

                if (rs.next()) {
                    return new PlayerInfo(rs.getString("thirdPartyUserId"), cid, rs.getInt("forbiddenTalkEndTime"), rs.getInt("forbiddenLoginEndTime"));
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

    public PlayerInfo getPlayerInfoFromDB(String passport) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call get_playerinfo(?)}");
                stmt.setString(1, passport);
                rs = stmt.executeQuery();

                if (rs.next()) {
                    return new PlayerInfo(passport, rs.getInt("cid"), rs.getInt("forbiddenTalkEndTime"), rs.getInt("forbiddenLoginEndTime"));
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

//     public boolean  insertPlayerInfo( String passport, int maxCid) {
//        CallableStatement stmt = null;
//        int rs = 0;
//        try {
//            if (connectToDB()) {
//                stmt = connection.prepareCall("{call insert_playerinfo(?,?,?)}");
//                stmt.setString(1, passport);
//                stmt.setInt(2, maxCid);
//                rs = stmt.executeUpdate();
//
//                if (rs == 1) {
//                    return true;
//                }
//            }
//        } catch (SQLException ex) {
//            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        } finally {
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return false;
//    }
//     public boolean getMaxCid(WebServer webServer) {
//        CallableStatement stmt = null;
//        ResultSet rs = null;
//        try {
//            if (connectToDB()) {
//                stmt = connection.prepareCall("{call get_maxcid()}");
//                rs = stmt.executeQuery();
//                if(rs.next()){
//                    webServer.maxCid = rs.getInt("maxcid") + 1;
//                } else {
//                    webServer.maxCid = 0;
//                }
//                return true;
//            }
//        } catch (SQLException ex) {
//            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return false;
//    }
//     public boolean  firstlogin(int thirdPartyId, String passport, int serverId, int remoteIP) {
//        CallableStatement stmt = null;
//        ResultSet rs = null;
//        try {
//            if (connectToDB()) {
//                stmt = connection.prepareCall("{call get_firstlogin(?,?,?)}");
//                stmt.setInt(1, thirdPartyId);
//                stmt.setString(2, passport);
//                stmt.setInt(3, serverId);
//                rs = stmt.executeQuery();
//                if(!rs.first()){
//                    stmt = connection.prepareCall("{call insert_firstlogin(?,?,?,?)}");
//                    stmt.setInt(1, thirdPartyId);
//                    stmt.setString(2, passport);
//                    stmt.setInt(3, serverId);
//                    stmt.setInt(4, remoteIP);
//                    if(stmt.executeUpdate() == 1){
//                        return true;
//                    }
//                }
//            }
//        } catch (SQLException ex) {
//            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return false;
//    }
//     public boolean  getTop10ByLevel(TopLevelPlayer[] topLevelPlayers) {
//        CallableStatement stmt = null;
//        ResultSet rs = null;
//        try {
//            if (connectToDB()) {
//                stmt = connection.prepareCall("{call gettopplayerbylevel()}");
//                rs = stmt.executeQuery();
//                int count = 0;
//                while(rs.next() && count < topLevelPlayers.length){
//                    topLevelPlayers[count] = new TopLevelPlayer(rs.getInt("mid"),rs.getString("name"),rs.getInt("gender"),rs.getInt("job"),rs.getInt("level"));
//                    count++;
//                }
//            }
//        } catch (SQLException ex) {
//            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return false;
//    }
    public List getServersHasLogin(String passport) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call getserviceshaslogin(?)}");
                stmt.setString(1, passport);
                List<Integer> serverList = new ArrayList<Integer>();
                rs = stmt.executeQuery();
                while (rs.next()) {
                    serverList.add(rs.getInt("serverId"));
                }
                return serverList;
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

    public void dbKeepAlive() {
        Statement stmt = null;
        try {
            if (connectToDB()) {
                stmt = connection.createStatement();
                stmt.executeQuery(Consts.DB_KEEPALIVE_TEST_STATEMENT);
            }
        } catch (SQLException ex) {
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
    }


    public List<String> getSimilarNamesByPlayerName(String playerName) {
        List<String> names = new ArrayList<String>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT name from charactor WHERE name like '%" + playerName + "%';";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                while (rs.next()) {
                    names.add(rs.getString("name"));
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return names;
    }

    public int getCidByPlayerName(String playerName) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT mid from charactor WHERE name like '%" + playerName + "%';";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                if (rs.next()) {
                    return rs.getInt("mid");
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return -1;
    }

    public String getPassportByCid(int cid) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT thirdPartyUserId from thirdpartyuser WHERE cid = " + cid + ";";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                if (rs.next()) {
                    return rs.getString("thirdPartyUserId");
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

//    public List<Integer> getHasLoginServers(String passport) {
//        Statement stmt = null;
//        ResultSet rs = null;
//        try {
//            if (connectToDB()) {
//                String sql = "SELECT serverId from firstlogin WHERE thirdPartyUserId = '" + passport + "';";
//                stmt = connection.createStatement();
//
//                rs = stmt.executeQuery(sql);
//                List<Integer> servers = new ArrayList<Integer>();
//                while (rs.next()) {
//                    servers.add(rs.getInt("serverId"));
//                }
//                return servers;
//            }
//        } catch (SQLException ex) {
//            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return null;
//    }
    public int getCidByPassport(String passport) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT cid from thirdpartyuser WHERE thirdPartyUserId = '" + passport + "';";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                if (rs.next()) {
                    return rs.getInt("cid");
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return -1;
    }

    public boolean insertGmOperateLog(String userName, int operate, String stringpara1) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call insert_operatelog(?,?,?)}");
                stmt.setString(1, userName);
                stmt.setInt(2, operate);
                stmt.setString(3, stringpara1);
                if (stmt.executeUpdate() == 1) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

    public OperateLogs getOperateLog(String userName, int start, int num) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call get_log(?,?,?)}");
                stmt.setString(1, userName);
                stmt.setInt(2, start);
                stmt.setInt(3, num);
                rs = stmt.executeQuery();
                OperateLogs logs = new OperateLogs();
                while (rs.next()) {
                    OperateLog log = new OperateLog();
                    log.time = rs.getString("time");
                    log.operate = rs.getInt("operate");
                    log.content = rs.getString("content");
                    logs.logs.add(log);
                }
                return logs;
            }
        } catch (SQLException ex) {
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

    public HashSet<String> getTrustedBrowserHosts() {
        HashSet<String> ips = new HashSet<String>();

        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT ip FROM trustedbrowserips;";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                while (rs.next()) {
                    ips.add(rs.getString("ip"));
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return ips;
    }

    public LinkedList<ServerConfig> getServerConfigs() {
        LinkedList<ServerConfig> serverConfigs = new LinkedList<ServerConfig>();

        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT id,regionid,name,dbhost,dbname,logdbhost,logdbnameprefix,host,externalhost,externalport,managerport,rpcport FROM serverconfig;";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                while (rs.next()) {
                    ServerConfig serverConfig = new ServerConfig();
                    serverConfig.id = rs.getInt("id");
                    serverConfig.regionid = rs.getString("regionid");
                    serverConfig.name = rs.getString("name");
                    serverConfig.dbHost = rs.getString("dbHost");
                    serverConfig.dbName = rs.getString("dbname");
                    serverConfig.logdbhost = rs.getString("logdbhost");
                    serverConfig.logDBNamePrefix = rs.getString("logdbnameprefix");
                    serverConfig.host = rs.getString("host");
                    serverConfig.externalHost = rs.getString("externalhost");
                    serverConfig.externalPort = rs.getInt("externalport");
                    serverConfig.managerPort = rs.getInt("managerport");
                    serverConfig.rpcPort = rs.getInt("rpcport");

                    serverConfigs.add(serverConfig);
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return serverConfigs;
    }

//    public List<Union> unionView(String unionName) {
//        CallableStatement stmt = null;
//        ResultSet rs = null;
//        List<Union> unions = null;
//        try {
//            if (connectToDB()) {
//                stmt = connection.prepareCall("{call get_unions()}");
//                rs = stmt.executeQuery();
//                if (rs.next()) {
//                    Blob blob = rs.getBlob(1);
//                    if (blob != null) {
//                        DBUnionsProto unionsProto = DBUnionsProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(blob.getBinaryStream())).build();
//                        if (unionsProto != null) {
//                            for (DBUnionDetailProto unionDetailProto : unionsProto.getUnionsList()) {
//                                if (unionDetailProto.getName().equalsIgnoreCase(unionName)) {
//                                    Union union = new Union(unionDetailProto);
//                                    if (unions == null) {
//                                        unions = new ArrayList<Union>();
//                                    }
//                                    unions.add(union);
//                                }
//                            }
//                        }
//                    }
//                }
//            }
//        } catch (Exception ex) {
//            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return unions;
//    }
//
//    public List<Union> allUnions() {
//        CallableStatement stmt = null;
//        ResultSet rs = null;
//        List<Union> unions = null;
//        try {
//            if (connectToDB()) {
//                stmt = connection.prepareCall("{call get_unions()}");
//                rs = stmt.executeQuery();
//                if (rs.next()) {
//                    Blob blob = rs.getBlob(1);
//                    if (blob != null) {
//                        DBUnionsProto unionsProto = DBUnionsProto.getDefaultInstance().newBuilderForType().mergeFrom(CodedInputStream.newInstance(blob.getBinaryStream())).build();
//                        if (unionsProto != null) {
//                            for (DBUnionDetailProto unionDetailProto : unionsProto.getUnionsList()) {
//                                Union union = new Union(unionDetailProto);
//                                if (unions == null) {
//                                    unions = new ArrayList<Union>();
//                                }
//                                unions.add(union);
//                            }
//                        }
//                    }
//                }
//            }
//        } catch (Exception ex) {
//            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
//            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }
//        }
//        return unions;
//    }

    public boolean modifyUser(String userName, Long privlg) {
        Statement stmt = null;
        try {
            if (connectToDB()) {
                String sql = "update user set privilege =" + privlg + " where name = \'" + userName + "\';" ;
                stmt = connection.createStatement();

                int count = stmt.executeUpdate(sql);
                if(count == 1){
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

    public boolean addUser(String userName, String password, int privlg) {
        Statement stmt = null;
        try {
            if (connectToDB()) {
                String sql = "replace into user values( \'" + userName + "\',\'" + password + "\'," + privlg + " );" ;
                stmt = connection.createStatement();

                int count = stmt.executeUpdate(sql);
                if(count > 0){
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

    public List<JsonUserData> getAllUser() {
        Statement stmt = null;
        ResultSet rs = null;
        List<JsonUserData> users = null;
        try {
            if (connectToDB()) {
                stmt = connection.createStatement();
                String sql = "select * from user;" ;
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                   String userName = rs.getString("name");
                   int privilege = rs.getInt("privilege");
                   JsonUserData userData = new JsonUserData(userName, privilege);
                   if(users == null){
                       users = new ArrayList<JsonUserData>();
                   }
                   users.add(userData);
                }
            }
        } catch (Exception ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return users;
    }

}
